﻿CREATE PROCEDURE dbo.sp_Update_Password
(
	@AccountID VARCHAR(10),
	@NewPassword VARCHAR(50)
)
AS
	SET NOCOUNT ON
	
	BEGIN TRAN T1
	
	IF EXISTS (SELECT * FROM PasswordHistory WHERE AccountID=@AccountID)
	BEGIN
		IF NOT EXISTS (SELECT * FROM PasswordHistory WHERE AccountID=@AccountID AND (PasswordP1 = @NewPassword OR PasswordP2 = @NewPassword OR PasswordP3 = @NewPassword))
		BEGIN
			UPDATE Accounts SET Password=@NewPassword, ForceChangePassword = 0, GraceLoginTimes = 0 WHERE AccountID=@AccountID;
			
			UPDATE PasswordHistory SET
			PasswordP3=PasswordP2, P3Date=P2Date, PasswordP2=PasswordP1, P2Date=P1Date, PasswordP1=@NewPassword, P1Date=GETDATE()
			WHERE AccountID=@AccountID
		END
		ELSE
		BEGIN
			RAISERROR('AccountID is invalid or the password has been used in the past 3 times.', 16, 1);
		END
	END
	ELSE
	BEGIN
		DECLARE @OldPassword VARCHAR(50)
		SELECT @OldPassword=Password FROM Accounts WHERE AccountID=@AccountID
		INSERT INTO PasswordHistory (AccountID, PasswordP1, P1Date) VALUES (@AccountID, @OldPassword, GETDATE())
		UPDATE Accounts SET Password=@NewPassword, ForceChangePassword = 0 WHERE AccountID=@AccountID;
	END
	
	IF (  @@ERROR <> 0 )
	BEGIN
		ROLLBACK TRAN T1
	END
	ELSE
	BEGIN
		COMMIT TRAN T1
	END